<?php
/**
 * Created by PhpStorm.
 * User: EZ
 * Date: 2017/5/27
 * Time: 10:24
 */
namespace core\db;
use PDO;
use core;
class Mysql{
    public $con=null;
    public $dns=null;
    public $contain=null;
    public $sql=null;
    // PDO连接参数
    protected $params = [
        PDO::ATTR_CASE              => PDO::CASE_NATURAL,
        PDO::ATTR_ERRMODE           => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_ORACLE_NULLS      => PDO::NULL_NATURAL,
        PDO::ATTR_STRINGIFY_FETCHES => false,
        PDO::ATTR_EMULATE_PREPARES  => false,//表示是否使用PHP本地模拟prepare
    ];

    // SQL表达式
    protected $selectSql    = 'SELECT%DISTINCT% %FIELD% FROM %TABLE%%FORCE%%JOIN%%WHERE%%GROUP%%HAVING%%ORDER%%LIMIT% %UNION%%LOCK%%COMMENT%';
    protected $insertSql    = '%INSERT% INTO %TABLE% (%FIELD%) VALUES (%DATA%) %COMMENT%';
    protected $insertAllSql = 'INSERT INTO %TABLE% (%FIELD%) %DATA% %COMMENT%';
    protected $updateSql    = 'UPDATE %TABLE% SET %SET% %JOIN% %WHERE% %ORDER%%LIMIT% %LOCK%%COMMENT%';
    protected $deleteSql    = 'DELETE FROM %TABLE% %USING% %JOIN% %WHERE% %ORDER%%LIMIT% %LOCK%%COMMENT%';


    public function __construct($config='')
    {

        $db_config=$config?$config:Config::get('db');

        $host=$db_config['host'];
        $db_name=$db_config['db_name'];
        $db_user=$db_config['db_user'];
        $db_pwd=$db_config['db_pwd'];

        $this->dns="mysql:host=$host;dbname=$db_name";
        try{
            if(!$this->con=new PDO($this->dns,$db_user,$db_pwd,$this->params)){
                throw new \PDOException("链接失败");
            } ;
        }catch(\PDOException $e){
            $e->getMessage();
        }

    }


    public function buildSql($sql=''){
        $table=isset($this->options['table'])?trim($this->options['table']):" ";
        $table || die('please set table name');
        $as=isset($this->options['as'])?trim($this->options['as']):" ";
        $fields=isset($this->options['fields'])?trim($this->options['fields']):" * ";
        $join=isset($this->options['join'])?trim($this->options['join']):"  ";
        $where=isset($this->options['where'])?trim($this->options['where']):"  ";
        $order=isset($this->options['order'])?trim($this->options['order']):"  ";
        $group=isset($this->options['group'])?trim($this->options['group']):"  ";
        $having=isset($this->options['having'])?trim($this->options['having']):"  ";
        $limit=isset($this->options['limit'])?trim($this->options['limit']):"  ";
        $this->sql=$sql?:'select '.$fields.' from '.$table.' '.$as.' '.$join.' '.$where.' '.$group.' '.$having.' '.$order.' '.$limit;
        return $this->sql;
    }



    /**
     * @param string $sql
     * 返回结果集
     */
    public function query($sql=''){
        $this->sql=$sql?$sql:$this->sql;
        return   self::$con->query($sql);
    }

    public function fetchAll($res){
        $res->setFetchMode(PDO::FETCH_ASSOC);
        $result_arr = $res->fetchAll();
        return $result_arr;
    }

    public function fetch($res){
        $res->setFetchMode(PDO::FETCH_ASSOC);
        $result_arr = $res->fetch();
        return $result_arr;
    }



    public function select($sql='',$field=''){
        $this->buildSql($sql);
        if($this->options['getSql']==true) return $this->sql;
        $res=$this->query($this->sql);
        $data=$this->fetchAll($res);
        if($field){
            $col=[];
            foreach($data as $k=>$v){
                if(isset($v[$field])){
                    $col[]=$v['field'];
                }else{
                    return false;
                }
            }
            return $col;
        }
        return $data;
    }

    /**
     * @param string $sql
     * @param string $field
     * @return bool|mixed|null
     */
    public function find($sql='',$field=''){
        $this->options['limit']=" limit 1";
        $this->buildSql($sql);
        if($this->options['getSql']==true) return $this->sql;
        $res=$this->query($this->sql);
        $data= $this->fetch($res);
        if($field){
            if(isset($data[$field])){
                return $data[$field];
            }else{
                return false;
            }
        }
        return $data;
    }



    public function limit($start,$row=null){
        if(is_null($row) && strpos($start,',')){
            list($s,$p)=explode(",",$start);
            $this->options['limit']=" limit $s,$p";
        }elseif(is_null($row) && is_numeric($start)){
            $this->options['limit']="limit $start ";
        }else{
            $this->options['limit']="limit $start,$row";
        }
        return $this;
    }

    public function table($table_name=''){
        $this->options['table']=$table_name?' `'.$table_name.'` ':'';

        return $this;
    }

    /**
     * @param $join
     * @param string $type
     * $join=[
     *      'inner join'=>['user','u','u.user_id=b.b_id'],
     *      'join'=>['']
     * ]
     */
    public function join($join,$type='inner join '){
        if(is_string($join)){
            $this->options['join']=$join;
        }
        if(is_array($join)){
            foreach($join as $k=>$v){
                if(is_array($v)){
                    $this->options['join'].=isset($v[2])?$k.' '.$v[0].' as '.$v[1].' ON '.$v[2]:$k.' '.$v[0].' ON '.$v[2];
                }else{
                    $this->options['join'].=$k.' '.$v;
                }
            }
        }
        return $this;
    }

    /**
     * @param $insert_array=['name'=>'aaa','age'=>25]
     * @return bool|int
     */

    public function insert($insert_array){

        if (!is_array($insert_array)) {
            return FALSE;
        }
        $fields = array();
        $value = array();
        foreach ($insert_array as $key => $val) {
            $fields[] = $this->parsekey($key);
            $value[] = $this->parsevalue($val);
        }
        $ignore=isset($this->options['ignore'])?$this->options['ignore']:' ';
        $sql='INSERT '.$ignore.' INTO '.$this->options['table']." (".join(',',$fields).") values(".join(',',$value).") ";
        if($this->options['getSql']==true){
            return $sql;
        }
        $res=self::$con->exec($sql);

        return $res;
    }
    public function getLastInsertId(){
        return self::$con->lastInsertId();
    }
    public function insertAll($insert_array){
        if (!is_array($insert_array)) {
            return FALSE;
        }
        $i=0;
        $fields=[];
        $value_str='';
        foreach($insert_array as $key=>$val){
            $value=[];
            if(is_array($val)){
                foreach($val as $k=>$v){
                    if($i==0){
                        $fields[]=$this->parseKey($k);
                    }
                    $value[] = $this->parsevalue($v);
                }
                $i=1;
            }
            $value_str[]="(".join(',',$value).") ";
        }
        $ignore=isset($this->options['ignore'])?$this->options['ignore']:'';
        $sql="INSERT $ignore INTO ".$this->options['table']." (".join(',',$fields).") values ".join(',',$value_str);
        if($this->options['getSql']=== true) return $sql;
        $res=self::$con->exec($sql);
        return $res;
    }

    public function buildInsertSql($fields,$values){
        $options=$this->options;
        $sql='insert into `'.$options['table'].'` ('.join(' , ',$fields).') values('.join(' , ',$values).') ';
    }


    public function data(){

    }


    public function distinct($p=true){
        $this->options['distinct']=$p?" distinct ":' ';
        return $this;
    }

    /**
     * @param bool $p
     * @return $this
     * $p==true的时候回返回要执行的sql语句（select/update/delete 语句）
     */
    public function getSql($p=false){
        $this->options['getSql'] = $p?true:false;
        return $this;
    }

    /**
     * @param bool $p
     * @return $this
     * 用于插入语句，表中如果有unique字段，用insert ignore into 会避免错误
     */
    public function ignore($p=false){
        $this->options['ignore']=$p?' ignore ':' ';
        return $this;
    }

    /**
     * @param string $where
     * $where=[
     *      'a.user'=>'aa',
     *      'a.uid'=>['in'=>$str]
     * ]
     */
    public function where($where=''){
        $this->options['where']=" where 1 ";

        if(is_array($where)){
            foreach($where as $k=>$v){
                $ks=explode('.',$k);
                $k_str='';
                if(isset($ks[1])){
                    $k_str=' `'.$ks[0].'`.`'.$ks[1].'` ';
                }else{
                    $k_str=' `'.$ks[0].'` ';
                }

                if(is_array($v)){
                    foreach($v as $k1=>$v1){
                        switch($k1){
                            case "in":
                            case 'not in':
                                $this->options['where'].=is_array($v1)?' and '.$k_str.$k1.' ('.'\''.implode("','",$v1).'\''.') ':' '.$k1.' ('.$v1.') ';
                                break;
                            case 'lt':
                                $this->options['where'].= " and $k_str > $v1 ";
                                break;
                            case 'gt':
                                $this->options['where'].=" and $k_str < $v1 ";
                                break;
                            case 'like':
                                $this->options['where'].=" and $k_str like '%".$v1."%' ";
                                break;
                        }
                    }

                }else{
                    $this->options['where'].=" and ".$k_str.' = '.$v;
                }
            }
        }else{
            $this->options['where'].=$where?' and '.$where:' ';
        }
        return $this;
    }

    public function _as($as=''){
        $this->options['as']=$as?" as `$as` ":'';
        return $this;
    }

    public function page(int $p,int $size){
        $p=$p>0?$p:1;
        $start=($p-1)*$size;
        $this->options['limit']= 'limit '.$start.','.$size;
        return $this;
    }

    public function _limit($start,$length=null){
        $this->options['limit']=' ';
        if(is_null($length) && strpos($start,',')){
            list($start,$length)= explode(',',$start);
            $this->options['limit']=" limit $start,$length ";
        }elseif($start && $length){
            $this->options['limit']="limit $start,$length ";
        }
        return $this;
    }

    public function fields($fields){
        if(!is_array($fields)){
            $this->options['fields']=$fields;
        }else{
            $this->options['fields']=join(',',$fields);
        }
        return $this;

    }
    public function parseKey(&$key) {
        $key = trim($key);
        if (!preg_match("/[,'\\\"\\*\\(\\)`.\\s]/", $key)) {
            $key = "`" . $key . "`";
        }
        return $key;
    }

    public function parseValue($value) {
        $value = addslashes(stripslashes($value));
        $value = "'" . $value . "'";
        return $value;
    }
    public function exec($sql){

        $res= self::$con->exec($sql);
        return $res;
    }

    public function update($data=[]){
        if(!is_array($data)){
            return false;
        }
        $table=isset($this->options['table'])?$this->options['table']:' ';
        $where=isset($this->options['where'])?$this->options['where']:' ';
        $table || die('please set table name');
        $update_sql='UPDATE '.$table;
        $update_ar=[];
        $update_str='';
        foreach($data as $k=>$v){
            $update_ar[]=$this->parseKey($k).'='."'".$v."' ";
        }
        $update_str=join(',',$update_ar);
        $update_sql='UPDATE '.$table.' set '.$update_str.' '.$where;
        if($this->options['getSql']==true) return $update_sql;

        $res=$this->exec($update_sql);
        return $res;
    }

    public function delete($sql=''){

    }

    public function getLastSql(){
        return $this->sql;
    }

    public function order($order){
        $this->options['order']=$order?" order by ".$order:' ';
        return $this;
    }

    public function group($group){
        $this->options['group']=$group?" group by $group":' ';
        return $this;
    }

    public function having($having){
        $this->options['having']=$having?" having  $having ":' ';
        return $this;
    }
    public function beginTransaction(){
        self::$con->beginTransaction();
    }
    public function commit(){
        self::$con->commit();
    }
    public function rollback(){
        self::$con->rollback();
    }
    public function lock($table,$type='r'){
        if(empty($table))die("error db:lock参数错误");
        if($type == 'w')$type = 'write';else $type='read';
        return $this->con->query('lock tables '.$table.' '.$type);
    }
    public function unlock(){
        $this->con->query('unlock tables');
    }

    public function __destruct()
    {

    }


}